﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F5AssemblyWTS : UserControl
    {

        private INISettings mySetting;
        private SqlConnection myConnection;

        public panel_F5AssemblyWTS()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.viewMode.SelectedIndex = 0;
            LoadData();
            this.F5WTS.PopupMenuShowing += F5WTS_PopupMenuShowing;
            this.f5AssemblyWTSContextMenu.ItemClicked += f5AssemblyWTSContextMenu_ItemClicked;
            this.F5WTS.DoubleClick += F5WTS_DoubleClick;
        }

        void F5WTS_DoubleClick(object sender, EventArgs e)
        {
            DataRow row = F5WTS.GetFocusedDataRow();
            frm_Edit_F5AssemblyWTS editForm = new frm_Edit_F5AssemblyWTS();
            editForm.f5wtsID = Int32.Parse(row[0].ToString());
            if (editForm.LoadData())
            {
                editForm.Show();
            }
        }

        void f5AssemblyWTSContextMenu_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.f5AssemblyWTSContextMenu.Hide();
            DataRow row = F5WTS.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {
                case "editSelection":                    
                    frm_Edit_F5AssemblyWTS editForm = new frm_Edit_F5AssemblyWTS();
                    editForm.f5wtsID = Int32.Parse(row[0].ToString());
                    if (editForm.LoadData())
                    {
                        editForm.Show();
                    } 
                    break;
                case "addSelection":
                    frm_Add_F5AssemblyWTS add_Form = new frm_Add_F5AssemblyWTS();                   
                    add_Form.Show();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void F5WTS_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.f5AssemblyWTSContextMenu.Show(MousePosition);
        }        
        
        ///////////////////////////////////////////
        /// Load Data From 231_F5TimeInput
        //////////////////////////////////////////
        private void LoadData()
        {
            gridF5WTS.DataSource = null;
            F5WTS.Columns.Clear();
            DataSet sourceDataSet = new DataSet();
            string sql = "SELECT [231_F5TimeInput].ID, [231_F5TimeInput].Date, [222_Staff].StaffName, [231_TimeWork].TimeWorkName,"
                + " [231_F5TimeInput].CompType, [231_F5TimeInput].OrderNo, [231_F5TimeInput].F5Time, [231_F5TimeInput].Number,"
                + " CAST(SUBSTRING([231_F5TimeInput].Number, CHARINDEX('-', [231_F5TimeInput].Number) + 1, LEN([231_F5TimeInput].Number)) AS INT) AS Quantity,"
                + " [231_F5TimeInput].Remark"
                + " FROM [231_F5TimeInput] LEFT OUTER JOIN"
                + " [222_Staff] ON [231_F5TimeInput].StaffID=[222_Staff].StaffID LEFT OUTER JOIN"
                + " [231_TimeWork] ON [231_F5TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                + " WHERE DATEDIFF(day, [231_F5TimeInput].Date, GETDATE()) < 365"
                + " ORDER BY [231_F5TimeInput].Date DESC";
            myConnection.Open();
            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd_sql;            
            adapter.Fill(sourceDataSet);
            adapter.Dispose();
            cmd_sql.Dispose();
            myConnection.Close();
            if (sourceDataSet != null)
            {
                gridF5WTS.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("Date", typeof(DateTime));
                newTable.Columns.Add("Staff", typeof(string));
                newTable.Columns.Add("TimeWorkName", typeof(string));
                newTable.Columns.Add("CompType", typeof(string));
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("F5Time", typeof(int));
                newTable.Columns.Add("Number", typeof(string));
                newTable.Columns.Add("Quantity", typeof(int));
                newTable.Columns.Add("Remark", typeof(string));
                gridF5WTS.DataSource = newTable;
            }
            F5WTS.Columns[0].Caption = "ID";
            F5WTS.Columns[1].Caption = "Date";
            F5WTS.Columns[2].Caption = "Person";
            F5WTS.Columns[3].Caption = "Time Work";
            F5WTS.Columns[4].Caption = "Comp Type";
            F5WTS.Columns[5].Caption = "Order No";
            F5WTS.Columns[6].Caption = "F5 Time";
            F5WTS.Columns[7].Caption = "Number";
            F5WTS.Columns[8].Caption = "Quantity";
            F5WTS.Columns[9].Caption = "Remark";
            F5WTS.Columns[0].Visible = false;
            F5WTS.BestFitColumns();
        }

        ///////////////////////////////////////////////////
        /// View Filter Data
        ///////////////////////////////////////////////////
        private void buttonView_Click(object sender, EventArgs e)
        {
            string sql = null;
            string error = null;
            
            gridF5WTS.DataSource = null;
            F5WTS.Columns.Clear();

            if (this.fromDate.EditValue == null)
            {
                error = "Please select From date";
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.toDate.EditValue == null)
                {
                    error = "Please select To date";
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                switch (this.viewMode.SelectedIndex)
                {
                    case 0:
                        sql = "SELECT [231_F5TimeInput].ID, [231_F5TimeInput].Date, [222_Staff].StaffName, [231_TimeWork].TimeWorkName,"
                            + " [231_F5TimeInput].OrderNo,"
                            + " CAST(SUBSTRING([231_F5TimeInput].Number, CHARINDEX('-', [231_F5TimeInput].Number) + 1, LEN([231_F5TimeInput].Number)) AS INT) AS Quantity,"
                            + " [231_F5TimeInput].F5Time, [231_F5TimeInput].Remark"
                            + " FROM [231_F5TimeInput] LEFT OUTER JOIN"
                            + " [222_Staff] ON [231_F5TimeInput].StaffID=[222_Staff].StaffID LEFT OUTER JOIN"
                            + " [231_TimeWork] ON [231_F5TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                            + " WHERE ([231_F5TimeInput].Date BETWEEN @FromDate AND @ToDate)"
                            + " ORDER BY [231_F5TimeInput].Date, [222_Staff].StaffName";
                        break;
                    case 1:
                        sql = "SELECT [231_F5TimeInput].ID, [231_F5TimeInput].Date, [222_Staff].StaffName,"
                            + " SUM([231_F5TimeInput].F5Time) AS TotalTime"
                            + " FROM [231_F5TimeInput] INNER JOIN"
                            + " [222_Staff] ON [231_F5TimeInput].StaffID = [222_Staff].StaffID"
                            + " GROUP BY [231_F5TimeInput].Date, [222_Staff].StaffName"
                            + " HAVING ([231_F5TimeInput].Date BETWEEN @FromDate AND @ToDate) "
                            + " ORDER BY [231_F5TimeInput].Date";
                        break;
                    case 2:
                        sql = "SELECT [231_F5TimeInput].ID, [231_F5TimeInput].OrderNo, [231_F5Plan].PartNo, [231_F5Plan].CommandQuantity,"
                            + " [222_Staff].StaffName, [231_TimeWork].TimeWorkName, [231_F5TimeInput].CompType,"
                            + " [231_F5TimeInput].Date, [231_F5Plan].FinishedDate"
                            + " FROM [231_F5Plan] INNER JOIN"
                            + " [231_F5TimeInput] ON [231_F5Plan].OrderNo = [231_F5TimeInput].OrderNo"
                            + " AND [231_F5Plan].FinishedDate < [231_F5TimeInput].Date INNER JOIN"
                            + " [222_Staff] ON [231_F5TimeInput].StaffID = [222_Staff].StaffID LEFT OUTER JOIN"
                            + " [231_TimeWork] ON [231_F5TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                            + " WHERE ([231_F5TimeInput].Date BETWEEN @FromDate AND @ToDate) AND ([231_F5TimeInput].OrderNo NOT LIKE N'Z%')";
                        break;
                }

                if (string.IsNullOrEmpty(sql))
                {
                    MessageBox.Show("Not found!");
                }
                else
                {
                    try
                    {
                        DataSet sourceDataSet = new DataSet();
                        myConnection.Open();
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd_sql;
                        cmd_sql.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = DateTime.Parse(this.fromDate.EditValue.ToString());
                        cmd_sql.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Parse(this.toDate.EditValue.ToString());
                        adapter.Fill(sourceDataSet);
                        adapter.Dispose();
                        cmd_sql.Dispose();
                        myConnection.Close();

                        if (sourceDataSet != null)
                        {
                            if (sourceDataSet.Tables[0].Rows.Count > 0)
                            {                                
                                gridF5WTS.DataSource = sourceDataSet.Tables[0];
                                F5WTS.Columns[0].Visible = false;
                                MessageBox.Show("Found " + sourceDataSet.Tables[0].Rows.Count + " results");
                                sourceDataSet.Tables.Clear();
                            }
                            else
                            {
                                if (this.viewMode.SelectedIndex == 2)
                                {
                                    gridF5WTS.DataSource = null;
                                    F5WTS.Columns.Clear();
                                    sourceDataSet.Tables.Clear();

                                    sql = "SELECT [231_F5TimeInput].ID, [231_F5TimeInput].Date, [222_Staff].StaffName, [231_TimeWork].TimeWorkName,"
                                        + " [231_F5TimeInput].OrderNo, [231_F5TimeInput].F5Time as [Working hours]"
                                        + " FROM [231_F5TimeInput] LEFT OUTER JOIN"
                                        + " [222_Staff] ON [222_Staff].StaffID=[231_F5TimeInput].StaffID LEFT OUTER JOIN"
                                        + " [231_TimeWork] ON [231_F5TimeInput].TimeWorkID=[231_TimeWork].TimeWorkID"
                                        + " WHERE ([231_F5TimeInput].Date BETWEEN @FromDate AND @ToDate)"
                                        + " ORDER BY [231_F5TimeInput].Date, [222_Staff].StaffID";
                                    myConnection.Open();
                                    cmd_sql = new SqlCommand(sql, myConnection);
                                    adapter = new SqlDataAdapter();
                                    adapter.SelectCommand = cmd_sql;
                                    cmd_sql.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = DateTime.Parse(this.fromDate.EditValue.ToString());
                                    cmd_sql.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Parse(this.toDate.EditValue.ToString());
                                    adapter.Fill(sourceDataSet);
                                    adapter.Dispose();
                                    cmd_sql.Dispose();
                                    myConnection.Close();
                                    if (sourceDataSet.Tables[0].Rows.Count > 0)
                                    {                                        
                                        gridF5WTS.DataSource = sourceDataSet.Tables[0];
                                        F5WTS.Columns[0].Visible = false;
                                        MessageBox.Show("Found " + sourceDataSet.Tables[0].Rows.Count + " results");
                                        sourceDataSet.Tables.Clear();
                                    }
                                    else
                                    {
                                        MessageBox.Show("Not Found!");
                                    }
                                }
                                else
                                {
                                    MessageBox.Show("Not Found!");
                                }
                            }
                        }
                        else
                        {
                            MessageBox.Show("Not Found!");
                        }
                        F5WTS.BestFitColumns();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //MessageBox.Show("Not Found!");
                    }
                }
            }
            else
            {
                MessageBox.Show(error);
            }          
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_F5AssemblyWTS add_form = new frm_Add_F5AssemblyWTS();
            add_form.Show();
        }        
    }
}
